﻿Public Class perDespesas
    Inherits AcessoBd

    Public Enum eTipoDespesa
        TODAS = 0
        CALCULO_AUTOMATICO = 1
        VALOR_MANUAL = 2
    End Enum

    ReadOnly Property sqlConsulta() As String
        Get

            Dim sSql As String
            sSql = " SELECT descricao as Despesa"
            sSql &= "  FROM Despesa"
            sSql &= " WHERE isnull(calculoAutomatico,0) = 0"
            sSql &= " ORDER BY codDespesa"
            Return sSql

        End Get
    End Property

    Public Function selecionarDespesas(ByVal tipoDespesa As eTipoDespesa) As DataTable
        Dim sSql As String = ""

        Try

            sSql = "   SELECT * "
            sSql &= "    FROM despesa "

            Select Case tipoDespesa
                Case eTipoDespesa.CALCULO_AUTOMATICO
                    sSql &= "    WHERE isnull(CalculoAutomatico,0) = 1"
                Case eTipoDespesa.VALOR_MANUAL
                    sSql &= "    WHERE isnull(CalculoAutomatico, 0) = 0"
            End Select
            sSql &= "ORDER BY codDespesa "

            Return MyBase.executarConsulta(sSql)

        Catch ex As Exception

            Throw New Exception("Ocorreu um erro ao selecionar despesas. " & _
                                    Environment.NewLine & ex.Message)


        End Try


    End Function

    Public Function retornarCodDespesa(ByVal sDespesa As String) As Integer
        Dim sSql As String = ""
        Dim dtRetorno As DataTable

        Try

            sSql = " SELECT codDespesa "
            sSql &= "  FROM despesa"
            sSql &= " WHERE descricao = @despesa"

            With MyBase.SQLCmd.Parameters
                .Clear()
                .AddWithValue("@despesa", sDespesa.Trim)
            End With

            dtRetorno = MyBase.executarConsulta(sSql)

            If dtRetorno.Rows.Count > 0 Then
                Return dtRetorno.Rows(0).Item("codDespesa")
            End If

        Catch ex As Exception

            Throw New Exception("Ocorreu um erro ao selecionar despesas. " & _
                                    Environment.NewLine & ex.Message)


        End Try


    End Function

    Public Function retornarDespesasProposta(ByVal iCodProposta As Int32, ByVal iRevisao As Int32) As DataTable
        Dim sSql As String

        sSql = "SELECT proposta_outrasDespesas.codDespesa" & Environment.NewLine &
               "      ,despesa.descricao " & Environment.NewLine &
               "      ,valorDespesa" & Environment.NewLine &
               "      ,gerarReceita" & Environment.NewLine &
               "      ,valorReceita" & Environment.NewLine &
               "  FROM proposta_outrasDespesas" & Environment.NewLine &
               "      INNER JOIN despesa on despesa.codDespesa = proposta_outrasDespesas.codDespesa" & Environment.NewLine &
               " WHERE codProposta = @codProposta" & Environment.NewLine &
               "   AND revProposta = @revProposta " & Environment.NewLine &
               "ORDER BY proposta_outrasDespesas.codDespesa"

        With SQLCmd.Parameters
            .Clear()
            .AddWithValue("codProposta", iCodProposta)
            .AddWithValue("revProposta", iRevisao)
        End With

        Return MyBase.executarConsulta(sSql)

    End Function


End Class
